package database;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;

import rmi.Answer;
import rmi.Question;
import structure.Course;
import structure.Exam;
import structure.Student;

public class JDBC {
	
	private Connection con;
	
	public JDBC(String server,String dbName, String username, String pass) throws ClassNotFoundException, SQLException {
		Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		
		String connectionUrl = "jdbc:sqlserver://"+server+";databaseName="+dbName+";";
		DriverManager.setLoginTimeout(5);
		con = DriverManager.getConnection(connectionUrl,username,pass);
	}
	
	public void closeConnection() throws SQLException{
			con.close();
	}
	
	public ResultSet execQuery(String query) throws SQLException{
		Statement st = con.createStatement();
		return st.executeQuery(query);
	}
	
	public ArrayList<Question> getQuestionsByType(int typeID) throws SQLException{
		String call = "{call GET_QUESTIONS_BY_TYPE(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, typeID);
		ResultSet rs = stat.executeQuery();
		ArrayList<Question> arrayQuestion = new ArrayList<>();
		while(rs.next()){
			Question temp = new Question();
			temp.setQuestionID(rs.getInt("QUESTION_ID"));
			temp.setQuestion(rs.getString("QUESTION_CONTENT"));
			temp.setQuestionImageLink(rs.getString("QUESTION_IMAGE"));
			temp.setQuestionType(rs.getInt("QT_ID"));
			temp.setPriority(rs.getInt("PRIORITY"));
			temp.setMapAnswers(getAnswersByQID(temp.getQuestionID()));
			arrayQuestion.add(temp);
		}
		return arrayQuestion;
	}
	
	public ArrayList<Question> getQuestionsWithNoAnswer() throws SQLException{
		String call = "{call GET_QUESTION_WITH_NO_ANSWER()}";
		CallableStatement stat = con.prepareCall(call);
		ResultSet rs = stat.executeQuery();
		ArrayList<Question> arrayQuestion = new ArrayList<>();
		while(rs.next()){
			Question temp = new Question();
			temp.setQuestionID(rs.getInt("QUESTION_ID"));
			temp.setQuestion(rs.getString("QUESTION_CONTENT"));
			temp.setQuestionImageLink(rs.getString("QUESTION_IMAGE"));
			temp.setQuestionType(rs.getInt("QT_ID"));
			temp.setPriority(rs.getInt("PRIORITY"));
			temp.setMapAnswers(getAnswersByQID(temp.getQuestionID()));
			arrayQuestion.add(temp);
		}
		return arrayQuestion;
	}
	
	public Question getQuestionsByQuestionID(int qID) throws SQLException{
		String call = "{call GET_QUESTIONS_BY_QID(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, qID);
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			Question temp = new Question();
			temp.setQuestionID(rs.getInt("QUESTION_ID"));
			temp.setQuestion(rs.getString("QUESTION_CONTENT"));
			temp.setQuestionImageLink(rs.getString("QUESTION_IMAGE"));
			temp.setQuestionType(rs.getInt("QT_ID"));
			temp.setPriority(rs.getInt("PRIORITY"));
			temp.setMapAnswers(getAnswersByQID(temp.getQuestionID()));
			return temp;
		}
		return null;
	}
	
	public ArrayList<Question> getQuestionsByExam(int examID) throws SQLException{
		String call = "{call GET_QUESTIONS_BY_EXAM_ID(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, examID);
		ResultSet rs = stat.executeQuery();
		ArrayList<Question> arrayQuestion = new ArrayList<>();
		while(rs.next()){
			Question temp = new Question();
			temp.setQuestionID(rs.getInt("QUESTION_ID"));
			temp.setQuestion(rs.getString("QUESTION_CONTENT"));
			temp.setQuestionImageLink(rs.getString("QUESTION_IMAGE"));
			temp.setQuestionType(rs.getInt("QT_ID"));
			temp.setPriority(rs.getInt("PRIORITY"));
			temp.setMapAnswers(getAnswersByQID(temp.getQuestionID()));
			arrayQuestion.add(temp);
		}
		return arrayQuestion;
	}
	
	public Question getQuestionsByAnswer(int aID) throws SQLException{
		String call = "{call GET_QUESTION_BY_ANSWER_ID(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, aID);
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			Question temp = new Question();
			temp.setQuestionID(rs.getInt("QUESTION_ID"));
			temp.setQuestion(rs.getString("QUESTION_CONTENT"));
			temp.setQuestionImageLink(rs.getString("QUESTION_IMAGE"));
			temp.setQuestionType(rs.getInt("QT_ID"));
			temp.setPriority(rs.getInt("PRIORITY"));
			temp.setMapAnswers(getAnswersByQID(temp.getQuestionID()));
			return temp;
		}
		return null;
	}
	
	public LinkedHashMap<Integer, Answer> getAnswersByQID(int qID) throws SQLException{
		String call = "{call GET_ANSWERS_BY_QID(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, qID);
		ResultSet rs = stat.executeQuery();
		LinkedHashMap<Integer, Answer> arrayAnswer = new LinkedHashMap<>();
		while(rs.next()){
			arrayAnswer.put(rs.getInt("ANSWER_ID"), new Answer(rs.getString("ANSWER_CONTENT"),rs.getBoolean("ISSHOW"),rs.getInt("QUESTION_ID")));
		}
		return arrayAnswer;
	}
	
	public LinkedHashMap<Integer, Answer> getAnswersByStudentID(int sID) throws SQLException{
		String call = "{call GET_ANSWER_BY_STUDENT_ID(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, sID);
		ResultSet rs = stat.executeQuery();
		LinkedHashMap<Integer, Answer> arrayAnswer = new LinkedHashMap<>();
		while(rs.next()){
			arrayAnswer.put(rs.getInt("ANSWER_ID"), new Answer(rs.getString("ANSWER_CONTENT"),rs.getBoolean("ISSHOW"),rs.getInt("QUESTION_ID")));
		}
		return arrayAnswer;
	}
	
	public ArrayList<Student> getStudentsByCourse(int cID) throws SQLException{
		String call = "{call GET_STUDENT_BY_COURSE(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, cID);
		ResultSet rs = stat.executeQuery();
		ArrayList<Student> temp = new ArrayList<Student>();
		
		while(rs.next()){
			Student stu = new Student();
			stu.setiD(rs.getInt("STUDENT_ID"));
			stu.setMSTS(rs.getString("ID"));
			stu.setFirstName(rs.getString("FIRSTNAME"));
			stu.setLastName(rs.getString("LASTNAME"));
			stu.setDateOfBirth(rs.getString("DATEOFBIRTH"));
			stu.setCMND(rs.getString("ID_NUMBER"));
			stu.setJoinedCourse(rs.getInt("COURSE_ID"));
			stu.setUsername(rs.getString("STUDENT_USER"));
			stu.setPassword(rs.getString("STUDENT_PASS"));
			temp.add(stu);
		}
		return temp;
	}
	
	public Student getStudentsByID(int sID) throws SQLException{
		String call = "{call GET_STUDENT_BY_ID(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, sID);
		ResultSet rs = stat.executeQuery();
		
		rs.next();
		Student stu = new Student();
		stu.setiD(rs.getInt("STUDENT_ID"));
		stu.setMSTS(rs.getString("ID"));
		stu.setFirstName(rs.getString("FIRSTNAME"));
		stu.setLastName(rs.getString("LASTNAME"));
		stu.setDateOfBirth(rs.getString("DATEOFBIRTH"));
		stu.setCMND(rs.getString("ID_NUMBER"));
		stu.setJoinedCourse(rs.getInt("COURSE_ID"));
		stu.setUsername(rs.getString("STUDENT_USER"));
		stu.setPassword(rs.getString("STUDENT_PASS"));
		return stu;
	}
	
	public Course getCourseByCourseID(int cID) throws SQLException{
		String call = "{call GET_COURSE_BY_COURSE_ID(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, cID);
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			Course temp = new Course();
			temp.setCourseID(rs.getInt("COURSE_ID"));
			temp.setCourseTitle(rs.getString("COURSE_TITLE"));
			temp.setDateOfExam(rs.getString("DATEOFEXAM"));
			temp.setTimeOfExam(rs.getString("TIMEOFEXAM"));
			temp.setRoom(rs.getString("ROOM"));
			temp.setDone(rs.getBoolean("DONE")); //need check
			return temp;
		}
		return null;
	}
	
	public ArrayList<Course> getCourses() throws SQLException{ //ko chac chan
		String call = "{call GET_ALL_COURSES()}"; //need check
		CallableStatement stat = con.prepareCall(call);
		ResultSet rs = stat.executeQuery();
		ArrayList<Course> array = new ArrayList<>();
		while(rs.next()){
			Course temp = new Course();
			temp.setCourseID(rs.getInt("COURSE_ID"));
			temp.setCourseTitle(rs.getString("COURSE_TITLE"));
			temp.setDateOfExam(rs.getString("DATEOFEXAM"));
			temp.setTimeOfExam(rs.getString("TIMEOFEXAM"));
			temp.setRoom(rs.getString("ROOM"));
			temp.setDone(rs.getBoolean("DONE")); 
			array.add(temp);
		}
		return array;
	}
	
	public ArrayList<Course> getCoursesByDone(boolean isDone) throws SQLException{ 
		String call = "{call GET_COURSE_BY_DONE(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setBoolean(1, isDone);
		ResultSet rs = stat.executeQuery();
		ArrayList<Course> array = new ArrayList<>();
		while(rs.next()){
			Course temp = new Course();
			temp.setCourseID(rs.getInt("COURSE_ID"));
			temp.setCourseTitle(rs.getString("COURSE_TITLE"));
			temp.setDateOfExam(rs.getString("DATEOFEXAM"));
			temp.setTimeOfExam(rs.getString("TIMEOFEXAM"));
			temp.setRoom(rs.getString("ROOM"));
			temp.setDone(rs.getBoolean("DONE")); 
			array.add(temp);
		}
		return array;
	}
	
	public Exam getExamByExamID(int eID) throws SQLException{
		String call = "{call GET_EXAMS_BY_EXAM_ID(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, eID);
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			Exam temp = new Exam();
			temp.setExamID(rs.getInt("EXAM_ID"));
			temp.setTitle(rs.getString("EXAM_TITLE"));
			temp.setExamTime(rs.getLong("EXAM_TIME"));
			return temp;
		}
		return null;
	}
	
	public ArrayList<Exam> getExams() throws SQLException{
		String call = "{call GET_EXAMS()}"; //need check
		CallableStatement stat = con.prepareCall(call);
		ResultSet rs = stat.executeQuery();
		ArrayList<Exam> array = new ArrayList<>();
		
		while(rs.next()){
			Exam temp = new Exam();
			temp.setExamID(rs.getInt("EXAM_ID"));
			temp.setTitle(rs.getString("EXAM_TITLE"));
			temp.setExamTime(rs.getLong("EXAM_TIME"));
			array.add(temp);
		}
		return array;
	}
	
	///////////////////////////
	public boolean InsertQuestion(String questionContent, String questionImage, int qType, int priority) {
		
		if(questionImage.length()>2000) return false;
		String call = "{call INSERT_QUESTION(?,?,?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setString(1, questionContent);
			stat.setString(2, questionImage);
			stat.setInt(3, qType);
			stat.setInt(4, priority);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean UpdateQuestion(int qID, String questionContent, String questionImage, int qType, int priority){
		if(questionImage.length()>2000) return false;
		String call = "{call UPDATE_QUESTION(?,?,?,?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, qID);
			stat.setString(2, questionContent);
			stat.setString(3, questionImage);
			stat.setInt(4, qType);
			stat.setInt(5, priority);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean DeleteQuestion(int qID){
		String call = "{call DELETE_QUESTION(?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, qID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}		
	}
	
	public boolean InsertAnswer(String answer, int qID, boolean isTrue,boolean isShow){
		if(answer.length()>500) return false;
		String call = "{call INSERT_ANSWER(?,?,?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setString(1, answer);
			stat.setInt(2, qID);
			stat.setBoolean(3, isTrue);
			stat.setBoolean(4, isShow);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean UpdateAnswer(int aID, String content, int qID, boolean result,boolean isShow){
		if(content.length()>500) return false;
		String call = "{call UPDATE_ANSWER(?,?,?,?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, aID);
			stat.setString(2, content);
			stat.setInt(3, qID);
			stat.setBoolean(4, result);
			stat.setBoolean(5, isShow);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean DeleteAnswer(int aID){
		String call = "{call DELETE_ANSWER(?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, aID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean InsertStudent(String iD, String firstName, String lastName, String dateOfBirth,
				String idNumber, int courseID, String user, String pass){
		if(iD.length()>10||firstName.length() >50 
				|| lastName.length() >50 || user.length() >20 
				|| pass.length() >20) return false;
		
		String call = "{call INSERT_STUDENT(?,?,?,?,?,?,?,?)}";
		CallableStatement stat;
		
		try {
			stat = con.prepareCall(call);
			stat.setString(1, iD);
			stat.setString(2, firstName);
			stat.setString(3, lastName);
			stat.setString(4, dateOfBirth);
			stat.setString(5, idNumber);
			stat.setInt(6, courseID);
			stat.setString(7, user);
			stat.setString(8, pass);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean UpdateStudent(int sID, String iD, String firstName, String lastName, String dateOfBirth,
			String idNumber, int courseID, double mark, String user, String pass){//need check
		if(firstName.length() >50 || lastName.length() >50 ) return false;
		String call = "{call UPDATE_STUDENT(?,?,?,?,?,?,?,?,?,?)}";
		CallableStatement stat;
		
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, sID);
			stat.setString(2, iD);
			stat.setString(3, firstName);
			stat.setString(4, lastName);
			stat.setString(5, dateOfBirth);
			stat.setString(6, idNumber);
			stat.setInt(7, courseID);
			BigDecimal a = BigDecimal.valueOf(mark);
			stat.setBigDecimal(8, a);
			stat.setString(9, user);
			stat.setString(10, pass);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean DeleteStudent(int sID){
		String call = "{call DELETE_STUDENT(?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, sID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean InsertCourse(String courseTitle, String dateOfExam, String timeOfExam,
			String room, boolean isDone){
		String call = "{call INSERT_COURSE(?,?,?,?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setString(1, courseTitle);
			stat.setString(2, dateOfExam);
			stat.setString(3, timeOfExam);
			stat.setString(4, room);
			stat.setBoolean(5, isDone);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean UpdateCourse(int cID, String courseTitle, String dateOfExam, String timeOfExam,
			String room, boolean isDone){
		String call = "{call UPDATE_COURSE(?,?,?,?,?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, cID);
			stat.setString(2, courseTitle);
			stat.setString(3, dateOfExam);
			stat.setString(4, timeOfExam);
			stat.setString(5, room);
			stat.setBoolean(6, isDone);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean DeleteCourse(int cID){
		String call = "{call DELETE_COURSE(?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, cID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean InsertExam(String title, int time){
		if(title.length()>200) return false;
		String call = "{call INSERT_EXAM(?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setString(1, title);
			stat.setInt(2, time);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean UpdateExam(int eID, String title,  int time){
		if(title.length()>200) return false;
		String call = "{call UPDATE_EXAM(?,?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, eID);
			stat.setString(2, title);
			stat.setInt(3, time);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean DeleteExam(int eID){
		String call = "{call DELETE_EXAM(?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, eID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean  InsertExamDetail(int examID, int questionID){
		String call = "{call INSERT_EXAM_DETAIL(?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, examID);
			stat.setInt(2, questionID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean DeleteExamDetail(int examDetailID){
		String call = "{call DELETE_EXAM_DETAIL(?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, examDetailID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean DeleteExamDetailByExamID(int examID){
		String call = "{call DELETE_EXAM_DETAIL_BY_EXAMID(?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, examID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean DeleteExamDetailByExamIDandQID(int examID,int qID){
		String call = "{call DELETE_EXAM_DETAIL_BY_EID_AND_QID(?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, examID);
			stat.setInt(2, qID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean InsertStudentLog(int sID, int aID){
		String call = "{call INSERT_STUDENT_LOG(?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, sID);
			stat.setInt(2, aID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean DeleteStudentLogByStudent(int sID){
		String call = "{call DELETE_STUDENT_LOG_BY_STUDENT(?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, sID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean DeleteStudentLogOnce(int sID, int aID){
		String call = "{call DELETE_STUDENT_LOG_ONCE(?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, sID);
			stat.setInt(2, aID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean CheckExistAnswer(int sID, int aID) throws SQLException{
		String call = "{call COUNT_AN_EXIST_ANSWER(?,?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, sID);
		stat.setInt(2, aID);
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			int count = rs.getInt(1);
			if(count!= 0) return true;
		}
		return false;
		
	}
	
	public LinkedHashMap<Integer, String> getAllQuestionTypes() throws SQLException{ 
		String call = "{call GET_ALL_QTYPE()}";
		CallableStatement stat = con.prepareCall(call);
		ResultSet rs = stat.executeQuery();
		LinkedHashMap<Integer, String> temp = new LinkedHashMap<>();
		while(rs.next()){
			temp.put(rs.getInt("QT_ID"), rs.getString("QT_NAME"));
		}
		return temp;
	}
	
	public boolean InsertQuestionType(String type){
		String call = "{call INSERT_QUESTION_TYPE(?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setString(1, type);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean DeleteQuestionType(int tID){
		String call = "{call DELETE_QUESTION_TYPE(?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, tID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean DeleteQuestionByType(int tID){
		String call = "{call DELETE_QUESTION_BY_TYPE(?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, tID);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public int GetTrueAnswerByQuestion(int qID) throws SQLException{
		String call = "{call GET_TRUE_ANSWER_FROM_QUESTION(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, qID);
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			return rs.getInt("ANSWER_ID");
		}
		return -1;
	}
	
	public boolean UpdateQuestionType(int tID,String name){
		if(name.trim().length() >100){
			return false;
		}
		String call = "{call UPDATE_QUESTION_TYPE(?,?)}";
		CallableStatement stat;
		try {
			stat = con.prepareCall(call);
			stat.setInt(1, tID);
			stat.setString(2, name);
			stat.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	public boolean CheckIfQuestionRemovable(int questionID) throws SQLException{
		String call = "{call CHECK_QUESTION_REMOVABLE(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, questionID);
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			int count = rs.getInt("NUM_OF_EXAM");
			if(count>=1) return false;
			else	return true;
		}
		return false;
	}
	
	public boolean CheckIfExamDetailExist(int exam, int qID) throws SQLException{
		String call = "{call CHECK_IF_EXAM_DETAIL_EXIST(?,?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, exam);
		stat.setInt(2, qID);
		ResultSet rs = stat.executeQuery();
		while(rs.next()){
			int count = rs.getInt("NUM_OF_DETAIL");
			if(count>=1) return true;
			else	return false;
		}
		return false;
	}
	
	public ArrayList<Course> GetLastestNonStuCourse() throws SQLException{
		String call = "{call GET_LATEST_NON_STU_COURSE_ID()}";
		CallableStatement stat = con.prepareCall(call);
		ResultSet rs = stat.executeQuery();
		ArrayList<Course> array = new ArrayList<>();
		while(rs.next()){
			Course temp = new Course();
			temp.setCourseID(rs.getInt("COURSE_ID"));
			temp.setCourseTitle(rs.getString("COURSE_TITLE"));
			temp.setDateOfExam(rs.getString("DATEOFEXAM"));
			temp.setTimeOfExam(rs.getString("TIMEOFEXAM"));
			temp.setRoom(rs.getString("ROOM"));
			temp.setDone(rs.getBoolean("DONE")); //need check
			array.add(temp);
		}
		return array;
	}
	
	public double getMarkofStudent(int studentID) throws SQLException{
		String call = "{call GET_MARK_OF_STUDENT(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setInt(1, studentID);
		ResultSet rs = stat.executeQuery();
		rs.next();
		
		return rs.getBigDecimal("MARK").doubleValue();
	}
	
	public int checkLogin(String user, String pass) throws SQLException{
		String call = "{call CHECK_LOGIN(?,?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setString(1, user);
		stat.setString(2, pass);
		
		ResultSet rs = stat.executeQuery();
		if(!rs.next()){
			return -1;
		}
		int rsint = rs.getInt("STUDENT_ID");
		return rsint;
	}
	
	public int checkUserName(String user) throws SQLException{
		String call = "{call CHECK_USERNAME(?)}";
		CallableStatement stat = con.prepareCall(call);
		stat.setString(1, user);
		
		ResultSet rs = stat.executeQuery();
		if(!rs.next()){
			return -1;
		}
		int rsint = rs.getInt("NUMOFUSERNAME");
		return rsint;
		
	}
}
